﻿﻿using System;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Runtime.InteropServices;


namespace Light.Framework
{
    public interface ISQLExecuter : IDisposable
    {
        string InvariantName { get; }
        string ConnectionString { get; }
        /// <summary>
        /// 默认为SQL语句，若执行存储过程请显示指定此属性值
        /// </summary>
        CommandType CommandType { get; set; }

        int Timeout { get; set; }

        Database DBMS { get; }

        void CacheParameterSet(string sql, params IDataParameter[] commandParameters);
        IDataParameter[] GetCachedParameterSet(string sql);

        IDbDataParameter CreateParameter();
        IDbDataParameter CreateParameter(string name, object value);
        IDbDataParameter[] CreateParameter(int size);
        void SetParameter(IDataParameter parame, string name, object value);
        void TransactionCommit();
        void TransactionRollback();

        object[] ExecuteArray(string sql);
        object[] ExecuteArray(string sql, params IDataParameter[] commandParameters);

        DataSet ExecuteDataset(string sql);
        DataSet ExecuteDataset(string sql, params IDataParameter[] commandParameters);

        DataTable ExecuteDatatable(string sql);
        DataTable ExecuteDatatable(string sql, params IDataParameter[] commandParameters);

        int ExecuteNonQuery(string sql);
        int ExecuteNonQuery(string sql, params IDataParameter[] commandParameters);

        IDataReader ExecuteReader(string sql);
        IDataReader ExecuteReader(string sql, params IDataParameter[] commandParameters);

        object ExecuteScalar(string sql);
        object ExecuteScalar(string sql, params IDataParameter[] commandParameters);

        void FillDatatable(DataTable datatable, string sql);
        void FillDatatable(DataTable datatable, string sql, params IDataParameter[] commandParameters);

        void FillDataset(DataSet dataset, string[] tableNames, string sql);
        void FillDataset(DataSet dataset, string[] tableNames, string sql, params IDataParameter[] commandParameters);

        int UpdateDataset(IDbCommand insertCommand, IDbCommand deleteCommand, IDbCommand updateCommand, DataSet dataset, string tableName);
        int UpdateDataset(string insertSql, string deleteSql, string updateSql, IDataParameter[] insertParameters, IDataParameter[] deleteParameters, IDataParameter[] updateParameters, DataSet dataset, string tableName);

        bool IsDb(Database db);
    }

    [ComVisible(false)]
    public abstract class SQLExecuter : ISQLExecuter
    {
        //private static Hashtable _paramCache = Hashtable.Synchronized(new Hashtable());
        private Hashtable _paramCache = new Hashtable();
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private string connectionString = Light.Utility.ApplicationSetting.ConnectionString;
        private IDbTransaction _transaction = null;
        private IsolationLevel _isolationLevel = IsolationLevel.ReadCommitted;
        private bool _useTransaction = false;
        private bool _mustRollBack = false;
        private bool _mustCloseConnection = true;

        protected readonly DbProviderFactory Factory = null;
        protected readonly IDbConnection DbConnection = null;

        private int _cmdTimeout = -1;
        private CommandType _commandType = CommandType.Text;

        public int Timeout
        {
            get { return _cmdTimeout; }
            set { _cmdTimeout = value; }
        }

        public CommandType CommandType
        {
            get { return _commandType; }
            set { _commandType = value; }
        }

        public Database DBMS
        {
            get
            {
                return SQLManager.GetDatabase(DbConnection);
            }
        }
        //public string ConnectionString
        //{
        //    get { return connectionString; }
        //    set { connectionString = value; }
        //}

        private IDbConnection _connection = null;

        public IDbConnection Connection
        {
            get
            {
                if (_connection == null || _connection.State == ConnectionState.Broken)
                {
                    _connection = SQLManager.GetDbConnection(); //CreateDbConnection();

                }
                if (_connection.State == ConnectionState.Closed)
                {
                    try
                    {
                        _connection.Open();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
                return _connection;
            }
            set { _connection = value; }
        }

        //public IDbConnection CreateDbConnection()
        //{
        //    DbConnection con = new SqlConnection(connectionString);
        //    return con;
        //}


        #region SQLExecuter
        protected SQLExecuter()
            : this(false)
        {
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="useTransaction">是否使用事务</param>
        protected SQLExecuter(bool useTransaction)
            : this(useTransaction, IsolationLevel.ReadCommitted)
        {
        }

        private static readonly object objCon = new object();

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="useTransaction"></param>
        /// <param name="level"></param>
        protected SQLExecuter(bool useTransaction, IsolationLevel level)
        {
            //解决多线程争用连接的问题
            lock (objCon)
            {
                DbConnection = Connection;
                Factory = DbProviderFactories.GetFactory(this.InvariantName);

                this.Init(useTransaction, level);
            }
        }

        protected SQLExecuter(IDbConnection connection)
            : this(connection, false)
        {
        }

        protected SQLExecuter(IDbConnection connection, bool useTransaction)
            : this(connection, useTransaction, IsolationLevel.ReadCommitted)
        {
        }

        protected SQLExecuter(IDbConnection connection, bool useTransaction, IsolationLevel level)
        {
            DbConnection = connection;
            Factory = DbProviderFactories.GetFactory(this.InvariantName);

            this.Init(useTransaction, level);
        }

        protected SQLExecuter(IDbConnection connection, bool useTransaction, IsolationLevel level, DbProviderFactory factory)
        {
            DbConnection = connection;
            Factory = factory;

            this.Init(useTransaction, level);
        }

        private void Init(bool useTransaction, IsolationLevel level)
        {
            _useTransaction = useTransaction;
            _isolationLevel = level;


        }
        #endregion

        #region Property
        public string InvariantName
        {
            get
            {
                if (DbConnection != null)
                    return DbConnection.GetType().Namespace;
                else
                    return String.Empty;
            }
        }

        public string ConnectionString
        {
            get
            {
                if (DbConnection != null)
                    return DbConnection.ConnectionString;
                else
                    return connectionString;
            }
        }
        #endregion

        #region FillDatatable
        public virtual void FillDatatable(DataTable datatable, string sql)
        {
            FillDatatable(datatable, sql, null);
        }

        public virtual void FillDatatable(DataTable datatable, string sql, params IDataParameter[] commandParameters)
        {
            if (datatable != null)
            {
                if (datatable.DataSet == null)
                {
                    DataSet dataset = new DataSet();
                    dataset.Tables.Add(datatable);
                }

                FillDataset(datatable.DataSet, new string[] { datatable.TableName }, sql, commandParameters);
            }
        }
        #endregion

        #region FillDataset
        public virtual void FillDataset(DataSet dataset, string[] tableNames, string sql)
        {
            FillDataset(dataset, tableNames, sql, null);
        }

        public virtual void FillDataset(DataSet dataset, string[] tableNames, string sql, params IDataParameter[] commandParameters)
        {
            if (String.IsNullOrEmpty(sql))
                throw new ArgumentNullException("sql");

            SQLLoger.Write(sql, commandParameters);

            if (dataset != null)
            {
                using (IDbCommand cmd = this.CreateDbCommand(sql))
                {
                    this.AttachParameters(cmd, commandParameters);
                    this.CleanParameterSyntax(cmd);

                    IDbDataAdapter da = CreateDbDataAdapter();
                    da.SelectCommand = cmd;
                    //da.MissingMappingAction = MissingMappingAction.Ignore;
                    da.MissingSchemaAction = MissingSchemaAction.Ignore;

                    if (tableNames != null && tableNames.Length > 0)
                    {
                        string tableName = "Table";
                        for (int index = 0; index < tableNames.Length; index++)
                        {
                            if (tableNames[index] == null || tableNames[index].Length == 0)
                                throw new ArgumentException("The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames");

                            da.TableMappings.Add(tableName + (index == 0 ? "" : index.ToString()), tableNames[index]);
                        }
                    }

                    try
                    {
                        da.Fill(dataset);
                    }
                    catch (Exception ex)
                    {
                        _mustRollBack = true;
                        throw ex;
                    }
                    finally
                    {
                        IDisposable id = da as IDisposable;
                        if (id != null)
                            id.Dispose();
                    }
                }
            }
        }
        #endregion

        #region ExecuteArray
        public virtual object[] ExecuteArray(string sql)
        {
            return ExecuteArray(sql, null);
        }

        public virtual object[] ExecuteArray(string sql, params IDataParameter[] commandParameters)
        {
            object[] objs = null;

            using (DataTable table = this.ExecuteDatatable(sql, commandParameters))
            {
                if (table != null && table.Rows.Count != 0)
                    objs = table.Rows[0].ItemArray;
            }

            if (objs != null && objs.Length != 0)
            {
                for (int i = 0; i < objs.Length; i++)
                {
                    if (objs[i] == DBNull.Value)
                        objs[i] = null;
                }
            }

            return objs;
        }
        #endregion

        #region ExecuteDatatable
        public virtual DataTable ExecuteDatatable(string sql)
        {
            return ExecuteDatatable(sql, null);
        }

        public virtual DataTable ExecuteDatatable(string sql, params IDataParameter[] commandParameters)
        {
            DataSet ds = ExecuteDataset(sql, commandParameters);
            if (ds != null && ds.Tables.Count != 0)
                return ds.Tables[0];
            else
                return null;
        }
        #endregion

        #region ExecuteDataset
        public virtual DataSet ExecuteDataset(string sql)
        {
            return ExecuteDataset(sql, null);
        }

        public virtual DataSet ExecuteDataset(string sql, params IDataParameter[] commandParameters)
        {
            if (String.IsNullOrEmpty(sql))
                throw new ArgumentNullException("sql");

            SQLLoger.Write(sql, commandParameters);

            using (IDbCommand cmd = this.CreateDbCommand(sql))
            {
                this.AttachParameters(cmd, commandParameters);
                this.CleanParameterSyntax(cmd);

                IDbDataAdapter da = CreateDbDataAdapter();
                da.SelectCommand = cmd;

                try
                {
                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    ClearDbCommand(cmd);

                    return ds;
                }
                catch
                {
                    _mustRollBack = true;
                    throw;
                }
                finally
                {
                    IDisposable id = da as IDisposable;
                    if (id != null)
                        id.Dispose();
                }
            }
        }
        #endregion

        #region ExecuteNonQuery
        public virtual int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, null);
        }

        public virtual int ExecuteNonQuery(string sql, params IDataParameter[] commandParameters)
        {
            if (String.IsNullOrEmpty(sql))
                throw new ArgumentNullException("sql");

            SQLLoger.Write(sql, commandParameters);

            using (IDbCommand cmd = this.CreateDbCommand(sql))
            {
                this.AttachParameters(cmd, commandParameters);
                this.CleanParameterSyntax(cmd);

                try
                {
                    int result = cmd.ExecuteNonQuery();
                    ClearDbCommand(cmd);
                    return result;
                }
                catch (Exception ex)
                {
                    _mustRollBack = true;
                    throw ex;
                }
            }
        }
        #endregion

        #region ExecuteScalar
        public virtual object ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, null);
        }

        public virtual object ExecuteScalar(string sql, params IDataParameter[] commandParameters)
        {
            if (String.IsNullOrEmpty(sql))
                throw new ArgumentNullException("sql");

            SQLLoger.Write(sql, commandParameters);

            using (IDbCommand cmd = this.CreateDbCommand(sql))
            {
                this.AttachParameters(cmd, commandParameters);
                this.CleanParameterSyntax(cmd);

                try
                {
                    object obj = cmd.ExecuteScalar();
                    ClearDbCommand(cmd);

                    if (obj != DBNull.Value)
                        return obj;
                    else
                        return null;
                }
                catch
                {
                    _mustRollBack = true;
                    throw;
                }
            }
        }
        #endregion

        #region ExecuteReader
        public virtual IDataReader ExecuteReader(string sql)
        {
            return ExecuteReader(sql, null);
        }

        public virtual IDataReader ExecuteReader(string sql, params IDataParameter[] commandParameters)
        {
            if (String.IsNullOrEmpty(sql))
                throw new ArgumentNullException("sql");

            SQLLoger.Write(sql, commandParameters);

            using (IDbCommand cmd = this.CreateDbCommand(sql))
            {
                this.AttachParameters(cmd, commandParameters);
                this.CleanParameterSyntax(cmd);

                IDataReader dataReader = null;
                try
                {
                    dataReader = cmd.ExecuteReader();
                }
                catch
                {
                    _mustRollBack = true;
                    throw;
                }

                ClearDbCommand(cmd);

                return dataReader;
            }
        }
        #endregion

        #region UpdateDataset
        public virtual int UpdateDataset(string insertSql, string deleteSql, string updateSql,
            IDataParameter[] insertParameters, IDataParameter[] deleteParameters, IDataParameter[] updateParameters,
            DataSet dataset, string tableName)
        {
            if (String.IsNullOrEmpty(tableName))
                throw new ArgumentNullException("tableName");

            if (String.IsNullOrEmpty(insertSql) || String.IsNullOrEmpty(deleteSql) || String.IsNullOrEmpty(updateSql))
                throw new ArgumentNullException("sql");

            IDbCommand insertCommand = this.CreateDbCommand(insertSql);
            this.AttachParameters(insertCommand, insertParameters);
            this.CleanParameterSyntax(insertCommand);

            IDbCommand deleteCommand = this.CreateDbCommand(deleteSql);
            this.AttachParameters(deleteCommand, deleteParameters);
            this.CleanParameterSyntax(deleteCommand);

            IDbCommand updateCommand = this.CreateDbCommand(updateSql);
            this.AttachParameters(updateCommand, updateParameters);
            this.CleanParameterSyntax(updateCommand);

            try
            {
                return UpdateDataset(insertCommand, deleteCommand, updateCommand, dataset, tableName);
            }
            finally
            {
                insertCommand.Dispose();
                deleteCommand.Dispose();
                updateCommand.Dispose();
            }
        }

        public virtual int UpdateDataset(IDbCommand insertCommand, IDbCommand deleteCommand, IDbCommand updateCommand,
            DataSet dataset, string tableName)
        {
            if (String.IsNullOrEmpty(tableName))
                throw new ArgumentNullException("tableName");

            int rowsAffected = 0;

            IDbDataAdapter da = CreateDbDataAdapter();
            da.InsertCommand = insertCommand;
            da.DeleteCommand = deleteCommand;
            da.UpdateCommand = updateCommand;

            try
            {
                if (da is DbDataAdapter)
                {
                    rowsAffected = ((DbDataAdapter)da).Update(dataset, tableName);
                }
                else
                {
                    da.TableMappings.Add(tableName, "Table");
                    rowsAffected = da.Update(dataset);
                }

                dataset.Tables[tableName].AcceptChanges();
            }
            catch
            {
                _mustRollBack = true;
                throw;
            }
            finally
            {
                IDisposable id = da as IDisposable;
                if (id != null)
                    id.Dispose();
            }

            return rowsAffected;
        }
        #endregion

        #region Transaction Commit/Rollback
        public virtual void TransactionCommit()
        {
            if (_transaction != null)
                _transaction.Commit();
        }

        public virtual void TransactionRollback()
        {
            if (_transaction != null)
                _transaction.Rollback();
        }
        #endregion

        #region CacheParameterSet
        public virtual void CacheParameterSet(string sql, params IDataParameter[] commandParameters)
        {
            if (String.IsNullOrEmpty(sql))
                throw new ArgumentNullException("sql");

            string hashKey = DbConnection.ConnectionString + ":" + sql;
            if (commandParameters.Length != 0)
                _paramCache[hashKey] = commandParameters;
            else
                _paramCache[hashKey] = null;
        }
        #endregion

        #region GetCachedParameterSet
        public virtual IDataParameter[] GetCachedParameterSet(string sql)
        {
            if (String.IsNullOrEmpty(sql))
                throw new ArgumentNullException("sql");

            string hashKey = DbConnection.ConnectionString + ":" + sql;
            IDataParameter[] cachedParameters = _paramCache[hashKey] as IDataParameter[];

            if (cachedParameters != null)
            {
                IDataParameter[] clonedParameters = new IDataParameter[cachedParameters.Length];

                for (int i = 0, j = cachedParameters.Length; i < j; i++)
                {
                    clonedParameters[i] = (IDataParameter)((ICloneable)cachedParameters[i]).Clone();
                }

                return clonedParameters;
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region CreateParameter
        public virtual IDbDataParameter CreateParameter()
        {
            IDbDataParameter[] parames = CreateParameter(1);
            return parames[0];
        }

        public virtual IDbDataParameter CreateParameter(string name, object value)
        {
            IDbDataParameter parame = CreateParameter();
            SetParameter(parame, name, value);

            return parame;
        }

        public void SetParameter(IDataParameter parame, string name, object value)
        {
            parame.ParameterName = name;

            if ((parame.GetType().Name == "OracleParameter"))
            {
                if (value is Guid)
                {
                    parame.DbType = DbType.AnsiStringFixedLength;
                    parame.Value = value.ToString();
                }
                //else if (value is String && ((string)value).Length == 0)
                //{
                //    parame.Value = new string(' ', 1);
                //}
                else
                {
                    parame.Value = value;
                }
            }
            else
            {
                parame.Value = value;
            }
        }

        public virtual IDbDataParameter[] CreateParameter(int size)
        {
            if (size <= 0)
                throw new ArgumentException("size");

            IDbDataParameter[] parameters = new IDbDataParameter[size];
            for (int i = 0; i < size; i++)
                parameters[i] = Factory.CreateParameter();

            return parameters;
        }

        #endregion

        #region CreateDbDataAdapter
        private IDbDataAdapter CreateDbDataAdapter()
        {
            return Factory.CreateDataAdapter();
        }
        #endregion

        #region CreateDbCommand
        private IDbCommand CreateDbCommand(string sql)
        {
            IDbCommand cmd = DbConnection.CreateCommand();
            if (_cmdTimeout >= 0)
                cmd.CommandTimeout = _cmdTimeout;

            if (DBMS != Database.Oracle) //FireBird shoud open conn befor begin transaction
            {
                OpenConn();
            }

            if (_useTransaction)
            {
                if (_transaction == null)
                {
                    _transaction = DbConnection.BeginTransaction(_isolationLevel);
                }

                cmd.Transaction = _transaction;

            }

            if (DBMS == Database.Oracle) // for avoid Oracle transaction errors 
            {
                OpenConn();
            }

            cmd.CommandType = CommandType;
            cmd.CommandText = sql;

            return cmd;
        }

        private void OpenConn()
        {
            if (DbConnection.State == ConnectionState.Closed)
            {
                DbConnection.Open();
                _mustCloseConnection = true;
            }
        }
        #endregion

        #region ClearDbCommand
        protected virtual void ClearDbCommand(IDbCommand cmd)
        {
            bool canClear = true;

            foreach (IDataParameter commandParameter in cmd.Parameters)
            {
                if (commandParameter.Direction != ParameterDirection.Input)
                {
                    canClear = false;
                    break;
                }
            }

            if (canClear)
            {
                cmd.Parameters.Clear();
            }
        }
        #endregion

        #region CleanParameterSyntax
        protected virtual void CleanParameterSyntax(IDbCommand cmd)
        {
            if (cmd.CommandType == CommandType.Text)
            {
                string name = cmd.GetType().Name;
                switch (name)
                {
                    case "SqlCommand ":
                        {
                            //do nothing
                        }
                        break;
                    case "OracleCommand":
                        {
                            cmd.CommandText = cmd.CommandText.Replace("@", ":");
                            if (cmd.Parameters.Count > 0)
                            {
                                foreach (IDataParameter parameter in cmd.Parameters)
                                {
                                    parameter.ParameterName = parameter.ParameterName.Replace("@", ":");
                                }
                            }
                        }
                        break;
                    //case "OleDbCommand":
                    //case " OdbcCommand":
                    default:
                        break;
                }
            }
        }
        #endregion

        #region AttachParameters
        private void AttachParameters(IDbCommand cmd, IDataParameter[] commandParameters)
        {

            if (commandParameters != null && commandParameters.Length != 0)
            {
                for (int i = 0; i < commandParameters.Length; i++)
                {
                    IDataParameter parame = commandParameters[i];

                    if (parame != null && parame.Value == null)
                        parame.Value = DBNull.Value;
                    else
                        SetParameter(parame, parame.ParameterName, parame.Value);

                    if (cmd.Parameters.Contains(parame.ParameterName))
                        cmd.Parameters[parame.ParameterName] = parame;
                    else
                    {
                        if (commandParameters[0] is DataParameter)
                        {
                            IDataParameter p = cmd.CreateParameter();
                            p.ParameterName = parame.ParameterName;
                            p.Value = parame.Value;
                            p.DbType = parame.DbType;
                            p.Direction = parame.Direction;
                            cmd.Parameters.Add(p);
                        }
                        else
                            cmd.Parameters.Add(parame);

                    }
                }
            }
        }
        #endregion

        #region ArrayConvert
        public static T[] ConvertAll<T>(object[] array)
        {
            if (array == null)
                return new T[] { };
            else
                return Array.ConvertAll<object, T>(array, ObjectConverter<T>);

            //return Array.ConvertAll<object, T>(array, obj => { return (T)obj; }); // .net 3.5
        }

        private static T ObjectConverter<T>(object input)
        {
            if (input != null)
                return (T)input;
            else
                return default(T);
        }
        #endregion

        #region ConvertValue

        public static object ConvertValue(string value, string type)
        {
            object obj = null;
            if (value != null)
            {
                if (!String.IsNullOrEmpty(type))
                {
                    int pos = type.LastIndexOf('.');
                    if (pos != -1 && pos < type.Length)
                        type = type.Substring(pos + 1);

                    type = type.ToLower();
                }

                try
                {
                    switch (type)
                    {
                        case "int16":
                        case "uint16":
                            obj = Convert.ToInt16(value);
                            break;
                        case "int32":
                        case "uint32":
                            obj = Convert.ToInt32(value);
                            break;
                        case "int64":
                        case "uint64":
                            obj = Convert.ToInt64(value);
                            break;
                        case "boolean":
                            obj = Convert.ToBoolean(value);
                            break;
                        case "datetime":
                            obj = Convert.ToDateTime(value);
                            break;
                        case "double":
                            obj = Convert.ToDouble(value);
                            break;
                        case "single":
                            obj = Convert.ToSingle(value);
                            break;
                        case "decimal":
                            obj = Convert.ToDecimal(value);
                            break;
                        default:
                            obj = value.ToString();
                            break;
                    }
                }
                catch (InvalidCastException)
                {
                }
            }

            return obj;
        }

        #endregion

        #region IDisposable
        public virtual void Dispose()
        {
            _paramCache.Clear();

            try
            {
                if (_transaction != null)
                {
                    try
                    {
                        if (_mustRollBack)
                        {
                            _transaction.Rollback();
                        }
                        else
                        {
                            _transaction.Commit();
                        }
                    }
                    finally
                    {
                        _transaction.Dispose();
                    }
                }

                if (DbConnection != null && _mustCloseConnection)
                {
                    if (DbConnection.State != ConnectionState.Closed)
                    {
                        DbConnection.Close();
                    }
                    DbConnection.Dispose();
                }

                if (Factory != null && Factory is IDisposable)
                {
                    ((IDisposable)Factory).Dispose();
                }
            }
            finally
            {
                //if (_sess != null)
                //{
                //    ActiveRecordMediator.GetSessionFactoryHolder().ReleaseSession(_sess);
                //}
            }
        }
        #endregion

        #region IsDb
        public bool IsDb(Database db)
        {
            return (db == this.DBMS);
        }
        #endregion
    }
}
